package kk.orm.util;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class SQLBuilder {

	public static String	primary_key	= "id";

	/**
	 * 根据entity内容，生成select sql语句
	 * 
	 * @param entity
	 * @return select sql语句
	 * 
	 * @参考sql SELECT 列名 FROM 表名 WHERE....<br>
	 *        SELECT LastName,FirstName FROM Persons
	 */
	public static String getSelectSQL(Class<?> clazz, String where) {
		StringBuilder sql = new StringBuilder();

		Object entity = new Object();
		try {
			entity = clazz.newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}

		sql.append("SELECT * FROM ");
		sql.append(ClassUtil.getTableName(entity.getClass()) + " ");

		if (where != null && !where.equals("")) {
			sql.append(" WHERE ");

			// 去掉句子中的';'
			where.replaceAll(";", "");

			// 将Limit语句调到order by后面，否则出错
			String upperWhere = where.toUpperCase();
			if (upperWhere.contains("LIMIT")) {
				int index = upperWhere.indexOf("LIMIT");
				String preLimit = where.substring(0, index);
				String limitSQL = where.substring(index, where.length());

				sql.append(preLimit);
				sql.append(" ORDER BY " + primary_key);
				sql.append(" " + limitSQL);
			} else {
				sql.append(where);
				sql.append(" ORDER BY " + primary_key + ";");
			}
		} else
			sql.append(" ORDER BY " + primary_key + ";");

		return sql.toString();
	}

	/** {@linkplain #getSelectSQL(Class, String)} */
	public static String getSelectSQL(Class<?> clazz) {
		return getSelectSQL(clazz, null);
	}

	/**
	 * 创建表 <br>
	 * <br>
	 * 例子:<br>
	 * CREATE TABLE IF NOT EXISTS User ( "id" INTEGER PRIMARY KEY AUTOINCREMENT,"age","name" ); //这是android的sql语句<br>
	 * <br>
	 * CREATE TABLE User(ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL, NAME VARCHAR(15),AGE INT) //这是mysql的sql语句
	 * 
	 * @param clazz
	 * @return
	 */
	public static String getCreatTableSQL(Class<?> clazz) {
		StringBuilder sql = new StringBuilder();

		String table = ClassUtil.getTableName(clazz);

		sql.append("CREATE TABLE IF NOT EXISTS ");
		sql.append(table);
		sql.append("( ");

		// 获取entity所有元素的Type和名字
		Field[] fields = ClassUtil.getDeclaredFields(clazz);
		fields = removePrimaryKey(fields);

		sql.append("\"" + primary_key + "\" INTEGER PRIMARY KEY AUTOINCREMENT,"); // 始终以id为自增长主键

		for (Field field : fields) {
			sql.append(field.getName() + " ");
			sql.append(FieldUtil.getSqlType(field));
			sql.append(",");
		}
		sql.deleteCharAt(sql.length() - 1);
		sql.append(" );");

		return sql.toString();
	}

	/**
	 * 获取 插入sql语句 <br>
	 * INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees');
	 * */
	public static String getInsertSQL(Object entity) {
		Class<?> clazz = entity.getClass();

		StringBuilder sql = new StringBuilder();

		sql.append("INSERT INTO ");
		sql.append(ClassUtil.getTableName(clazz));// Persons
		sql.append(getPropertiesNameSql(clazz));// (LastName, Address)
		sql.append(" VALUES ");
		sql.append(getValuesSql(entity)); // ('Wilson', 'Champs-Elysees')
		sql.append(";");

		return sql.toString();
	}

	/**
	 * 删除表sql语句<br>
	 * 参考: {@link http://www.w3school.com.cn/sql/sql_drop.asp}<br>
	 * 
	 * SQL DROP TABLE 语句 ： DROP TABLE 表名称<br>
	 * （表的结构、属性以及索引也会被删除） <br>
	 * <br>
	 * 
	 * SQL TRUNCATE TABLE 语句: TRUNCATE TABLE 表名称<br>
	 * （仅仅删除表格中的数据）
	 * */
	public static <T> String getDropTableSQL(Class<T> clazz) {
		StringBuilder sql = new StringBuilder();
		sql.append("DROP TABLE ");
		sql.append(ClassUtil.getTableName(clazz));
		sql.append(";");

		return sql.toString();
	}

	/** 获取属性的值==>('Wilson', 'Champs-Elysees') */
	private static String getValuesSql(Object entity) {
		Class<?> clazz = entity.getClass();

		StringBuilder sb = new StringBuilder();
		sb.append("(");

		Field[] fields = ClassUtil.getDeclaredFields(clazz);
		fields = removePrimaryKey(fields);

		for (Field field : fields) {
			field.setAccessible(true);

			Object value = new Object();
			try {
				value = field.get(entity);
			} catch (Exception e) {
				e.printStackTrace();
			}

			sb.append("\"" + value + "\""); // 全部当字符串处理,sql会自己转换类型的
			sb.append(",");
		}
		sb.deleteCharAt(sb.length() - 1);
		sb.append(")");

		return sb.toString();
	}

	/** 例如,User有id,name,age三个成员,返回(name,age) */
	public static String getPropertiesNameSql(Class<?> clazz) {
		StringBuilder sb = new StringBuilder();
		sb.append("(");

		Field[] fields = ClassUtil.getDeclaredFields(clazz);
		fields = removePrimaryKey(fields);

		for (Field field : fields) {
			sb.append(field.getName());
			sb.append(",");
		}

		sb.deleteCharAt(sb.length() - 1);
		sb.append(")");

		return sb.toString();
	}

	/**
	 * DELETE FROM Person WHERE id="xx"; // id为主键
	 * 
	 * @param entity
	 * @return
	 */
	public static String getDeleteSQL(Object entity) {
		// 获取主键的值
		// Field field = FieldUtil.getField(SQLBuilder.primary_key, entity.getClass());
		Object primaryKeyValue = FieldUtil.getValue(entity, primary_key);

		StringBuilder where = new StringBuilder();
		if (primaryKeyValue != null && !primaryKeyValue.equals("")) {
			where.append(primary_key);
			where.append(" = ");
			where.append("\"" + primaryKeyValue + "\"");
		}

		return getDeleteSqlByWhere(entity.getClass(), where.toString());
	}

	/**
	 * DELETE FROM Person WHERE LastName = 'Wilson';
	 * 
	 * @param entity
	 * @return
	 */
	public static <T> String getDeleteSqlByWhere(Class<T> clazz, String where) {
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM ");
		sql.append(ClassUtil.getTableName(clazz));

		sql.append(" WHERE ");
		if (where != null && !where.equals("")) {
			sql.append(where);
		} else {
			sql.append(primary_key + "=\"\"");// 如果找不到id也没关系, id="",sql不会执行任何操作
		}
		sql.append(";");

		return sql.toString();
	}

	/**
	 * 删除表中所有数据<br>
	 * DELETE FROM 表名称
	 * 
	 * @param clazz
	 * @return
	 */
	public static <T> String getDeletAllSQL(Class<T> clazz) {
		return "DELETE FROM " + ClassUtil.getTableName(clazz);
	}

	/** {@linkplain #getUpdateSQLByWhere(Object, String)} */
	public static String getUpdateSQL(Object entity) {
		return getUpdateSQLByWhere(entity, null);
	}

	/***
	 * UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'
	 * 
	 * @param entity
	 * @return
	 */
	public static String getUpdateSQLByWhere(Object entity, String where) {
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE " + ClassUtil.getTableName(entity.getClass()) + " SET ");

		Field[] fields = ClassUtil.getDeclaredFields(entity.getClass());
		fields = removePrimaryKey(fields);

		for (Field field : fields) {
			Object value = FieldUtil.getValue(entity, field.getName());

			sql.append(field.getName());
			sql.append("=");
			sql.append("\"" + value + "\"");
			sql.append(",");
		}
		sql.deleteCharAt(sql.length() - 1);

		sql.append(" WHERE ");
		if (where != null && !where.equals("")) {
			sql.append(where);
		} else {
			Object primaryKeyValue = FieldUtil.getValue(entity, primary_key);

			sql.append(primary_key + "=");
			sql.append("\"" + (primaryKeyValue == null ? "\"\"" : primaryKeyValue) + "\"");// 如果找不到id也没关系, id="",sql不会执行任何操作
		}
		sql.append(";");

		return sql.toString();
	}

	/** 去掉主键 */
	private static Field[] removePrimaryKey(Field[] fields) {
		List<Field> list = new ArrayList<Field>(Arrays.asList(fields));

		for (int i = 0; i < list.size(); i++) {
			Field field = list.get(i);
			String name = field.getName();
			if (name.equals(primary_key)) {
				list.remove(i--);
			}
		}

		return list.toArray(new Field[0]);
	}
}
